// 员工的增删改查

// 引入express
const express = require("express");
// 引入数据库操作封装
const db = require("../utils/db.js");
//创建router对象
const router = express.Router();
//配置router路由
router.get("/query", (req, res) => {
    res.send("员工")
})

// 查询员工信息
router.get("/search", (req, res) => {
    let {
        name,
        sex,
        age,
        idCard,
        departmentName,
        experience,
        rankName,
        major,
        education,
        school
    } = req.query;
    let sql=`select e.*,d.departmentName,d.departmentCount from employee e,department d where e.depID = d.id`;
    if(name){
        sql+=` and name like '%${name}%'`;
    }
    if(sex){
        sql+=` and sex ='${sex}'`;
    }
    if(age){
        sql+=` and age=${age}`;
    }
    if(idCard){
        sql+=` and idCard like '%${idCard}%'`;
    }

    if(departmentName){
        sql+=` and departmentName like '%${departmentName}%'`
    }
    if(experience){
        sql+=` and experience like '%${experience}%'`;
    }
    if(rankName){
        sql+=` and rankName like '%${rankName}%'`;
    }
    if(major){
        sql+=` and major like '%${major}%'`;
    }
    if(education){
        sql+=` and education like '%${education}%'`;
    }
    if(school){
        sql+=` and school like '%${school}%'`;
    }
    console.log(sql)
    db.execute(sql,(rst)=>{
        res.send({
            code:0,
            msg:"成功",
            data:rst
        })
    })
})





//添加
router.post("/save", (req, res) => {
    let {
        name,
        sex,
        age,
        idCard,
        depID,
        experience,
        rankName,
        major,
        education,
        school,
        tel,
        NativePlace,
        remarks
    } = req.body;
    let sql = `insert into employee (name,sex,age,idCard,depID,experience,rankName,major,education,school,tel,NativePlace,remarks) values ('${name}','${sex}','${age}','${idCard}','${depID}','${experience}','${rankName}','${major}','${education}','${school}','${tel}','${NativePlace}','${remarks}')`;
    db.execute(sql, (rst) => {
        if (rst.affectedRows != 0) {
            res.send({
                code: 0,
                msg: "添加成功"
            })
        } else {
            res.send({
                code: 1,
                msg: "添加失败"
            })
        }
    })
})


router.get("/findOne",(req,res)=>{
   let id= req.query.id;
   let sql=`select * from employee where id=${id}`;
   db.execute(sql,(rst)=>{
       res.send({
           code:0,
           data:rst[0]
       })
   })
})

// 修改数据
//添加
router.post("/update", (req, res) => {
    let {
        id,
        name,
        sex,
        age,
        idCard,
        depID,
        experience,
        rankName,
        major,
        education,
        school,
        tel,
        NativePlace,
        remarks
    } = req.body;
    let sql = `update employee set name='${name}',sex='${sex}',age='${age}',idCard='${idCard}',depID='${depID}',experience='${experience}',rankName='${rankName}',major='${major}',education='${education}',school='${school}',tel='${tel}',NativePlace='${NativePlace}',remarks ='${remarks}' where id = ${id}`;
    db.execute(sql, (rst) => {
        if (rst.affectedRows != 0) {
            res.send({
                code: 0,
                msg: "修改成功"
            })
        } else {
            res.send({
                code: 1,
                msg: "修改失败"
            })
        }
    })
})

//暴露路由
module.exports = router;